'\" t
.\"     Title: \fBmysqldbcopy\fR
.\"    Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\"      Date: 01/14/2017
.\"    Manual: MySQL Utilities
.\"    Source: MySQL 1.6.4
.\"  Language: English
.\"
.TH "\FBMYSQLDBCOPY\FR" "1" "01/14/2017" "MySQL 1\&.6\&.4" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqldbcopy \- Copy Database Objects Between Servers
.SH "SYNOPSIS"
.HP \w'\fBmysqldbcopy\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIdb_name\fR\fR\fB\ [\fR\fB\fI:new_db_name\fR\fR\fB]\fR\ 'u
\fBmysqldbcopy [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fI:new_db_name\fR\fR\fB]\fR
.SH "DESCRIPTION"
.PP
This utility copies a database on a source server to a database on a destination server\&. If the source and destination servers are different, the database names can be the same or different\&. If the source and destination servers are the same, the database names must be different\&.
.PP
The utility accepts one or more database pairs on the command line\&. To name a database pair, use
\fIdb_name\fR:\fInew_db_name\fR
syntax to specify the source and destination names explicitly\&. If the source and destination database names are the same,
\fIdb_name\fR
can be used as shorthand for
\fIdb_name\fR:\fIdb_name\fR\&.
.PP
By default, the operation copies all objects (tables, views, triggers, events, procedures, functions, and database\-level grants) and data to the destination server\&. There are options to turn off copying any or all of the objects as well as not copying the data\&.
.PP
To exclude specific objects by name, use the
\fB\-\-exclude\fR
option with a name in
\fIdb\fR\&.*obj* format, or you can supply a search pattern\&. For example,
\fB\-\-exclude=db1\&.trig1\fR
excludes the single trigger and
\fB\-\-exclude=trig_\fR
excludes all objects from all databases having a name that begins with
trig
and has a following character\&.
.PP
By default, the utility creates each table on the destination server using the same storage engine as the original table\&. To override this and specify the storage engine to use for all tables created on the destination server, use the
\fB\-\-new\-storage\-engine\fR
option\&. If the destination server supports the new engine, all tables use that engine\&.
.PP
To specify the storage engine to use for tables for which the destination server does not support the original storage engine on the source server, use the
\fB\-\-default\-storage\-engine\fR
option\&.
.PP
The
\fB\-\-new\-storage\-engine\fR
option takes precedence over
\fB\-\-default\-storage\-engine\fR
if both are given\&.
.PP
If the
\fB\-\-new\-storage\-engine\fR
or
\fB\-\-default\-storage\-engine\fR
option is given and the destination server does not support the specified storage engine, a warning is issued and the server\*(Aqs default storage engine setting is used instead\&.
.PP
By default, the operation uses a consistent snapshot to read the source databases\&. To change the locking mode, use the
\fB\-\-locking\fR
option with a locking type value\&. Use a value of
\fBno\-locks\fR
to turn off locking altogether or
\fBlock\-all\fR
to use only table locks\&. The default value is
\fBsnapshot\fR\&. Additionally, the utility uses WRITE locks to lock the destination tables during the copy\&.
.PP
You can include replication statements for copying data among a master and slave or between slaves\&. The
\fB\-\-rpl\fR
option permits you to select from the following replication statements to include in the export\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBmaster\fR
.sp
Create and execute a
\fBCHANGE MASTER\fR
statement to make the destination server a slave of the server specified in the
\fB\-\-source\fR
option\&. This executes the appropriate STOP and START slave statements\&. The
\fBSTOP SLAVE\fR
statement is executed at the start of the copy and the
\fBCHANGE MASTER\fR
followed by the
\fBSTART SLAVE\fR
statements are executed after the copy\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBslave\fR
.sp
Create and execute a
\fBCHANGE MASTER\fR
statement to make the destination server a slave connected to the same master as the server specified in the
\fB\-\-source\fR
option\&. This executes the appropriate STOP and START slave statements\&. The STOP SLAVE statement is executed at the start of the copy and the
\fBCHANGE MASTER\fR
followed by the
\fBSTART SLAVE\fR
statements after the copy\&.
.RE
.PP
To include the replication user in the
\fBCHANGE MASTER\fR
statement, use the
\fB\-\-rpl\-user\fR
option to specify the user and password\&. If this option is omitted, the utility attempts to identify the replication user\&. In the event that there are multiple candidates or the user requires a password, the utility aborts with an error\&.
.PP
If you attempt to copy databases on a server with GTIDs enabled (GTID_MODE = ON), a warning is generated if the copy does not include all databases\&. This is because the GTID statements generated include the GTIDs for all databases and not only those databases in the export\&.
.PP
The utility also generates a warning if you copy databases on a GTID enabled server but use the
\fB\-\-skip\-gtid \fR
option\&.
.PP
To make the most use of GTIDs, you should copy all of the databases on the server with the
\fB\-\-all\fR
option\&.
OPTIONS.PP
\fBmysqldbcopy\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-character\-set=\fIcharset\fR
.sp
Sets the client character set\&. The default is retrieved from the server variable
character_set_client\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-default\-storage\-engine=\fIdef_engine\fR
.sp
The engine to use for tables if the destination server does not support the original storage engine on the source server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-destination=\fIdestination\fR
.sp
Connection information for the destination server\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example :
\fIlogin\-path\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example :
\fIconfiguration\-file\-path\fR[:\fIsection\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example :
\fIuser\fR[:\fIpasswd\fR]@\fIhost\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-exclude=\fIexclude\fR, \-x\fIexclude\fR
.sp
Exclude one or more objects from the operation using either a specific name such as db1\&.t1 or a search pattern\&. Use this option multiple times to specify multiple exclusions\&. By default, patterns use
\fBLIKE\fR
matching\&. With the
\fB\-\-regexp\fR
option, patterns use
\fBREGEXP\fR
matching\&.
.sp
This option does not apply to grants\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
The utility attempts to determine if the pattern supplied has any special characters (such as an asterisks), which may indicate that the pattern could be a REGEXP pattern\&. If there are special, non\-SQL LIKE pattern characters and the user has not specified the
\fB\-\-regexp\fR
option, a warning is presented to suggest the user check the pattern for possible use with the
\fB\-\-regexp\fR
option\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-drop\-first
.sp
Drop each database to be copied if exists before copying anything into it\&. Without this option, an error occurs if you attempt to copy objects into an existing database\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
Before MySQL Utilities 1\&.4\&.2, this option was named
\-\-force\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-locking=\fIlocking\fR
.sp
Choose the lock type for the operation\&. Permitted lock values are
\fBno\-locks\fR
(do not use any table locks),
\fBlock\-all\fR
(use table locks but no transaction and no consistent read), and
\fBsnapshot\fR
(consistent read using a single transaction)\&. The default is
\fBsnapshot\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-multiprocess
.sp
Specify the number of processes to concurrently copy the specified databases\&. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default \- no concurrency)\&. Multiprocessing works at the database level for Windows and at the table level for Non\-Windows (POSIX) systems\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-new\-storage\-engine=\fInew_engine\fR
.sp
The engine to use for all tables created on the destination server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-not\-null\-blobs
.sp
Allow conversion of blob fields marked as NOT NULL to NULL before copy then restore NOT NULL after the copy\&. May cause indexes to be rebuilt if the affected blob fields are used in indexes\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-quiet, \-q
.sp
Turn off all messages for quiet execution\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-regexp, \-\-basic\-regexp, \-G
.sp
Perform pattern matches using the
\fBREGEXP\fR
operator\&. The default is to use
\fBLIKE\fR
for matching\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-rpl=\fIdump_option\fR, \-\-replication=\fIdump_option\fR
.sp
Include replication information\&. Permitted values are
\fBmaster\fR
(make destination a slave of the source server) and
\fBslave\fR
(make destination a slave of the same master as the source \- only works if the source server is a slave)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-rpl\-user=\fIreplication_user\fR
.sp
The user and password for the replication user requirement in the form:
\fIuser\fR[:\fIpassword\fR] or
\fIlogin\-path\fR\&. E\&.g\&. rpl:passwd Default = None\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
l \-\-skip\-gtid
.sp
Skip creation and execution of GTID statements during the copy operation\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-all
.sp
Copy all of the databases on the server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip=\fIobjects\fR
.sp
Specify objects to skip in the operation as a comma\-separated list (no spaces)\&. Permitted values are
\fBCREATE_DB\fR,
\fBDATA\fR,
\fBEVENTS\fR,
\fBFUNCTIONS\fR,
\fBGRANTS\fR,
\fBPROCEDURES\fR,
\fBTABLES\fR,
\fBTRIGGERS\fR, and
\fBVIEWS\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-source=\fIsource\fR
.sp
Connection information for the source server\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example :
\fIlogin\-path\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example :
\fIconfiguration\-file\-path\fR[:\fIsection\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example :
\fIuser\fR[:\fIpasswd\fR]@\fIhost\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-ca
.sp
The path to a file that contains a list of trusted SSL CAs\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL certificate file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-key
.sp
The name of the SSL key file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl
.sp
Specifies if the server connection requires use of SSL\&. If an encrypted connection cannot be established, the connection attempt fails\&. Default setting is 0 (SSL not required)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-verbose, \-v
.sp
Specify how much information to display\&. Use this option multiple times to increase the amount of information\&. For example,
\fB\-v\fR
= verbose,
\fB\-vv\fR
= more verbose,
\fB\-vvv\fR
= debug\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-version
.sp
Display version information and exit\&.
.RE
NOTES.PP
You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation\&.
.PP
On the source to copy all objects from the database, the user must have these privileges:
\fBSELECT\fR
for tables,
\fBSHOW VIEW\fR
for views,
\fBEVENT\fR
for events and
\fBTRIGGER\fR
for triggers\&. Additionally, the
\fBSELECT\fR
privilege is also required for the
mysql
database\&.
.PP
On the destination to copy all objects, the user must have these privileges:
\fBCREATE\fR,
\fBALTER\fR,
\fBSELECT\fR,
\fBINSERT\fR,
\fBUPDATE\fR,
\fBLOCK TABLES\fR,
\fBDROP\fR
if
\fB\-\-drop\-first\fR
option is used,
\fBSUPER\fR
when binary logging is enabled,
\fBCREATE VIEW\fR
for views,
\fBCREATE ROUTINE\fR,
\fBEXECUTE\fR
for procedures and functions,
\fBEVENT\fR
for events,
\fBTRIGGER\fR
for triggers and
\fBGRANT OPTION\fR
to copy grants\&. The
\fBSUPER\fR
privilege might also be required for some objects (views, procedures, functions, events and triggers), depending on their
\fBDEFINER\fR
value\&.
.PP
Actual privileges required may differ from installation to installation depending on the security privileges present and whether the database contains certain objects such as views or events and whether binary logging is enabled\&.
.PP
The
\fB\-\-new\-storage\-engine\fR
and
\fB\-\-default\-storage\-engine\fR
options apply to all destination tables in the operation\&.
.PP
Some option combinations may result in errors during the operation\&. For example, eliminating tables but not views may result in an error a the view is copied\&.
.PP
The
\fB\-\-rpl\fR
option is not valid for copying databases on the same server\&. If used in this manner, an error is generated\&.
.PP
When copying data and including the GTID commands, you may encounter an error similar to "GTID_PURGED can only be set when GTID_EXECUTED is empty"\&. This occurs because the destination server is not in a clean replication state\&. To alleviate this problem, you can issue a "RESET MASTER" command on the destination prior to executing the copy\&.
.PP
Cloning databases that contain foreign key constraints does not change the constraint in the cloned table\&. For example, if table db1\&.t1 has a foreign key constraint on table db1\&.t2, when db1 is cloned to db2, table db2\&.t1 contains a foreign key constraint on db1\&.t2\&.
.PP
Copying tables that contain 0 in auto_increment columns is accomplished by enabling the
NO_AUTO_VALUE_ON_ZERO
SQL_MODE prior to the copy and disabling it after\&.
.PP
The path to the MySQL client tools should be included in the
PATH
environment variable in order to use the authentication mechanism with login\-paths\&. This permits the utility to use the
\fBmy_print_defaults\fR
tools which is required to read the login\-path values from the login configuration file (\&.mylogin\&.cnf)\&.
.PP
If any database identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (\fB`\fR)\&. In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i\&.e\&. (\fB"\fR) in Windows or (\fB\*(Aq\fR) in non\-Windows systems, in order for the utilities to read backtick quoted identifiers as a single argument\&. For example, to copy a database with the name
\fBweird`db\&.name\fR
with
\fBother:weird`db\&.name\fR, the database pair must be specified using the following syntax (in non\-Windows):
\fB\*(Aq`weird``db\&.name`:`other:weird``db\&.name`\*(Aq\fR\&.
.PP
Keep in mind that you can only take advantage of multiprocessing if your system has multiple CPUs available for concurrent execution\&. Also note that multiprocessing is applied at a different level according to the operating system where the mysqldbcopy utility is executed (due to python limitations)\&. In particular, it is applied at the database level for Windows (i\&.e\&., different databases are concurrently copied) and at the table level for Non\-Windows (POSIX) systems (i\&.e\&., different tables within the same database are concurrently copied)\&.
EXAMPLES.PP
The following example demonstrates how to use the utility to copy a database named
util_test
to a new database named
util_test_copy
on the same server:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcopy \e\fR
  \fB\-\-source=root:pass@localhost:3310:/test123/mysql\&.sock \e\fR
  \fB\-\-destination=root:pass@localhost:3310:/test123/mysql\&.sock \e\fR
  \fButil_test:util_test_copy\fR
# Source on localhost: \&.\&.\&. connected\&.
# Destination on localhost: \&.\&.\&. connected\&.
# Copying database util_test renamed as util_test_copy
# Copying TABLE util_test\&.t1
# Copying table data\&.
# Copying TABLE util_test\&.t2
# Copying table data\&.
# Copying TABLE util_test\&.t3
# Copying table data\&.
# Copying TABLE util_test\&.t4
# Copying table data\&.
# Copying VIEW util_test\&.v1
# Copying TRIGGER util_test\&.trg
# Copying PROCEDURE util_test\&.p1
# Copying FUNCTION util_test\&.f1
# Copying EVENT util_test\&.e1
# Copying GRANTS from util_test
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
If the database to be copied does not contain only InnoDB tables and you want to ensure data integrity of the copied data by locking the tables during the read step, add a
\fB\-\-locking=lock\-all\fR
option to the command:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcopy \e\fR
  \fB\-\-source=root:pass@localhost:3310:/test123/mysql\&.sock \e\fR
  \fB\-\-destination=root:pass@localhost:3310:/test123/mysql\&.sock \e\fR
  \fButil_test:util_test_copy \-\-locking=lock\-all\fR
# Source on localhost: \&.\&.\&. connected\&.
# Destination on localhost: \&.\&.\&. connected\&.
# Copying database util_test renamed as util_test_copy
# Copying TABLE util_test\&.t1
# Copying table data\&.
# Copying TABLE util_test\&.t2
# Copying table data\&.
# Copying TABLE util_test\&.t3
# Copying table data\&.
# Copying TABLE util_test\&.t4
# Copying table data\&.
# Copying VIEW util_test\&.v1
# Copying TRIGGER util_test\&.trg
# Copying PROCEDURE util_test\&.p1
# Copying FUNCTION util_test\&.f1
# Copying EVENT util_test\&.e1
# Copying GRANTS from util_test
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
To copy one or more databases from a master to a slave, you can use the following command to copy the databases\&. Use the master as the source and the slave as the destination:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcopy \-\-source=root@localhost:3310 \e\fR
  \fB\-\-destination=root@localhost:3311 test123 \-\-rpl=master \e\fR
  \fB\-\-rpl\-user=rpl\fR
# Source on localhost: \&.\&.\&. connected\&.
# Destination on localhost: \&.\&.\&. connected\&.
# Source on localhost: \&.\&.\&. connected\&.
# Stopping slave
# Copying database test123
# Copying TABLE test123\&.t1
# Copying data for TABLE test123\&.t1
# Connecting to the current server as master
# Starting slave
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
To copy a database from one slave to another attached to the same master, you can use the following command using the slave with the database to be copied as the source and the slave where the database needs to copied to as the destination:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcopy \-\-source=root@localhost:3311 \e\fR
  \fB\-\-destination=root@localhost:3312 test123 \-\-rpl=slave \e\fR
  \fB\-\-rpl\-user=rpl\fR
# Source on localhost: \&.\&.\&. connected\&.
# Destination on localhost: \&.\&.\&. connected\&.
# Source on localhost: \&.\&.\&. connected\&.
# Stopping slave
# Copying database test123
# Copying TABLE test123\&.t1
# Copying data for TABLE test123\&.t1
# Connecting to the current server\*(Aqs master
# Starting slave
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.sp
LIMITATIONS.PP
When copying tables with blob fields defined as
\fBNOT NULL\fR
and the
\fB\-\-not\-null\-blobs\fR
option is not specified, the copy operation stops with an error suggesting how to manually remove the restriction\&.
.PP
However, you can use the
\fB\-\-not\-null\-blobs\fR
option to automatically convert the fields\&. In this case, the utility prints a warning that the fields were temporarily altered on the destination to remove the
\fBNOT NULL\fR
option\&. The
\fINOT NULL\fR
restriction is restored after the copy\&.
.PP
This special processing is needed because the copy attempts to use a bulk insert technique to copy the data in two passes; one to copy the data without blob field data, and another to update the rows with the blob data\&. Thus, having blob fields defined as
\fBNOT NULL\fR
fails on the first pass unless the restriction is removed before the data is copied\&.
PERMISSIONS REQUIRED.PP
The user must have SELECT, SHOW VIEW, EVENT and TRIGGER privileges for the database(s) on the source server\&. On the destination server, the user must have the following privileges for the copied database(s): CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if
\fB\-\-drop\-first\fR
option is used, and SUPER depending on the objects DEFINER value\&. When copying tables that include foreign keys, the user must also have the REFERENCES privilege\&.
.SH "COPYRIGHT"
.br
.PP
Copyright \(co 2006, 2017, Oracle and/or its affiliates. All rights reserved.
.PP
This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
.PP
This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
.PP
You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.
.sp
.SH "SEE ALSO"
For more information, please refer to the MySQL Utilities and Fabric
documentation, which is available online at
http://dev.mysql.com/doc/index-utils-fabric.html
.SH AUTHOR
Oracle Corporation (http://dev.mysql.com/).
